Snowflakeのテーブル名取得方法を調べてみた

Snowflakeでテーブル名の一覧取得をどうすれば良いのか気になったので、ちょっと調べてみました。

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#ビジネス・アナリティクス

#Snowflake

大高大輔

2020.02.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！DA(データアナリティクス)事業本部インテグレーション部の大高です。

Snowflakeでテーブル名の一覧取得をどうすれば良いのか気になったので、ちょっと調べてみました。

大きく2つあり、SHOW TABLESでの取得と各データベース内のINFORMATION_SCHEMA内のビューTABLESで取得できるようです。それぞれ見ていきたいと思います。

SHOW TABLES

１つ目は「SHOW TABLES」コマンドでの取得になります。下記のヘルプにある通り、様々なSHOW XXXXXのコマンドのうちの一つで自分がアクセス権限をもつテーブルの一覧が取得できます。

SHOW TABLES — Snowflake Documentation

SHOW [ TERSE ] TABLES [ HISTORY ] [ LIKE '<pattern>' ]
                                  [ IN { ACCOUNT | DATABASE [ <db_name> ] | [ SCHEMA ] [ <schema_name> ] } ]
                                  [ STARTS WITH '<name_string>' ]
                                  [ LIMIT <rows> [ FROM '<name_string>' ] ]

ぱっと見て、あまり見かけないTERSEとHISTORYいうパラメータがありますね。

TERSE

これを記述すると出力されるカラムが簡潔になるオプションのようです。具体的にはそれぞれ以下のようになります。

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	automatic_clustering	change_tracking
2019-04-22 16:01:59.825 -0700	CALL_CENTER	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	TABLE		LINEAR(CC_CALL_CENTER_SK)	60	11776		1	ON	OFF
2019-04-22 16:02:02.350 -0700	CATALOG_PAGE	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	TABLE		LINEAR(CP_CATALOG_PAGE_SK)	50000	2777088		1	ON	OFF
2019-04-22 16:02:03.063 -0700	CATALOG_RETURNS	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	TABLE		LINEAR(CR_RETURNED_DATE_SK, CR_ITEM_SK)	14405363575	828692696576		1	ON	OFF

created_on	name	kind	database_name	schema_name
2019-04-22 16:01:59.825 -0700	CALL_CENTER	TABLE	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL
2019-04-22 16:02:02.350 -0700	CATALOG_PAGE	TABLE	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL
2019-04-22 16:02:03.063 -0700	CATALOG_RETURNS	TABLE	SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL

TERSEをつけると大分スッキリしますね。なお「現在このパラメータはサポートされていません。」という記述がヘルプに見られましたが2020/02/12時点では動くようでした。

HISTORY

このパラメータを付けると、タイムトラベル機能での保持期限内であれば、DROP済みでPERGEしてしていないテーブルも表示できます。

出力にdropped_onカラムが追加され、ドロップしていないテーブルであればNULLが、ドロップしたテーブルであればドロップした日時が出力されます。

INFORMATION_SCHEMA.TABLES

２つ目は「INFORMATION_SCHEMA」の「TABLES」ビューから取得する方法です。

Information Schema — Snowflake Documentation

こちらは、以下のように「DATABASE」を指定するので、その「DATABASE」配下のテーブルを取得します。また、Warehouseも利用する必要があります。

USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA INFORMATION_SCHEMA;
SELECT * FROM TABLES;

結果は以下のようになります。「SHOW TABLES」に比べて細かい情報が取得できるのが分かります。また、取得できるレコードとして「INFORMATION_SCHEMA」配下の「VIEW」もレコードとして取得されました。

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_OWNER	TABLE_TYPE	IS_TRANSIENT	CLUSTERING_KEY	ROW_COUNT	BYTES	RETENTION_TIME	SELF_REFERENCING_COLUMN_NAME	REFERENCE_GENERATION	USER_DEFINED_TYPE_CATALOG	USER_DEFINED_TYPE_SCHEMA	USER_DEFINED_TYPE_NAME	IS_INSERTABLE_INTO	IS_TYPED	COMMIT_ACTION	CREATED	LAST_ALTERED	AUTO_CLUSTERING_ON	COMMENT
SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	CALL_CENTER		BASE TABLE	NO	LINEAR(CC_CALL_CENTER_SK)	60	11776	1						YES	YES		2019-04-22 16:01:59.825 -0700	2019-09-23 12:48:56.455 -0700	YES	
SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	CATALOG_PAGE		BASE TABLE	NO	LINEAR(CP_CATALOG_PAGE_SK)	50000	2777088	1						YES	YES		2019-04-22 16:02:02.350 -0700	2019-09-23 12:48:56.458 -0700	YES	
SNOWFLAKE_SAMPLE_DATA	TPCDS_SF100TCL	CATALOG_RETURNS		BASE TABLE	NO	LINEAR(CR_RETURNED_DATE_SK, CR_ITEM_SK)	14405363575	828692696576	1						YES	YES		2019-04-22 16:02:03.063 -0700	2019-09-23 12:48:56.460 -0700	YES